package com.hsnn.medstgmini.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
 *@category Excel公用类
 *@author 邱磊
 *@date 2015年6月15日 下午7:50:36
 */
public class ExcelUtil {
	/**
	 * 
	 *@category 读取Excel，支持（xls和xlsx格式）
	 *@author 邱磊
	 *@date 2015年6月15日 下午7:49:54
	 *@param inputStream
	 *@param fileName
	 *@param sheetNum
	 *@return
	 *@throws FileNotFoundException
	 */
	public static List<Map<String, String>> readExcelFile(InputStream inputStream ,String fileName,
			int sheetNum) throws FileNotFoundException {
		List<Map<String, String>> ls = new ArrayList<Map<String, String>>();
		Workbook book = null;
		try {
			if(fileName.toLowerCase().endsWith("xls")){ 
				book = new HSSFWorkbook(inputStream);  
				} 
			if(fileName.toLowerCase().endsWith("xlsx")){  
				book = new XSSFWorkbook(inputStream); 
				}    
			Sheet sheet = book.getSheetAt(sheetNum);
			int rowNum = 0;
			int jjj = sheet.getLastRowNum();
			System.out.println(jjj);
			for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
				Row row = sheet.getRow(i);
				Map<String, String> rowMap = new HashMap<String, String>();
				if (null == row){
					continue;
				}
				for (int j = 0; j < row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j);
					String content = "";
					if (null != cell){
						cell.setCellType(cell.CELL_TYPE_STRING);
						content = cell.toString();
					}

					if (content.equals("")) {
						rowNum++;
					}
					if (StringUtils.isNotBlank(content)) {
						rowMap.put(String.valueOf(j + 1), content);
					}

				}
				if (rowNum == row.getLastCellNum()) {
					rowMap.clear();
				}
				rowNum = 0;

				if (!rowMap.isEmpty()) {
					ls.add(rowMap);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != inputStream){
				try {
					inputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return ls;
	}
	
	/**
	 * 
	 *@category 导出excel
	 *@author wangbing
	 *@date 2015年6月15日 下午7:49:54
	 *@param page
	 *@param os
	 *@param title
	 *@param headers
	 *@param alias
	 *@return
	 * @throws IOException 
	 *@throws FileNotFoundException
	 */
	@SuppressWarnings("unchecked")
	public static void exportExcel(Pagination page,OutputStream os,String title,String[] headers,String[] alias) throws IOException{
		SXSSFWorkbook  wb = new SXSSFWorkbook (100);  
		Sheet  sheet = wb.createSheet(title);  
        Row row = sheet.createRow((int) 0);  
        CellStyle style = wb.createCellStyle();  
        style.setAlignment(CellStyle.ALIGN_CENTER); 
        
        Cell cell = null;
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Map<String,Object>> list = (List<Map<String, Object>>) page.getRows();
        for (int i = 0; i < list.size(); i++) {  
            row = sheet.createRow((int) i + 1);  
            Map<String,Object> data = list.get(i);  
            for(int j = 0;j<alias.length;j++){
            	row.createCell(j).setCellValue(data.get(alias[j])==null?"":data.get(alias[j]).toString());  
            }
            
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/drugSpec.xls");  
            wb.write(os);  
            os.close();  
            wb.dispose();
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}
	/**
     * 
     * @category: ie,chrom,firfox下处理文件名显示乱码
     * @Title: processFileName
     */
    public static String processFileName(HttpServletRequest request, String fileNames) {
        String codedfilename = null;
        try {
            String agent = request.getHeader("USER-AGENT");
            if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
                    && -1 != agent.indexOf("Trident")) {// ie

                String name = java.net.URLEncoder.encode(fileNames, "UTF8");

                codedfilename = name;
            } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等


                codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return codedfilename;
    }
	public static void main(String[] args) throws IOException {
		FileInputStream fileInputStream = new FileInputStream(new File("d:\\aaa.xlsx"));
		Long start = System.currentTimeMillis();
		List<Map<String, String>> list1 = ExcelUtil.readExcelFile(fileInputStream,"aaa.xlsx", 0);
		for (int i = 0; i < list1.size(); i++){
			Map<String, String> map = list1.get(i);
			for (int j = 0; j < map.size(); j++){
				System.out.print(map.get(j+1)+"——");
			}
			System.out.println();
		}
		Long end = System.currentTimeMillis();
	}
	
}
